1. Field of the Invention
The field of the invention is data processing, or, more specifically, methods, systems, and products for materialized query table matching.
2. Description Of Related Art
The development of the EDVAC computer system of 1948 is often cited as the beginning of the computer era. Since that time, computer systems have evolved into extremely complicated devices. Today's computers are much more sophisticated than early systems such as the EDVAC. The most basic requirements levied upon computer systems, however, remain little changed. A computer system's job is to access, manipulate, and store information. Computer system designers are constantly striving to improve the way in which a computer system can deal with information.
Information stored on a computer system is often organized in a structure called a database. A database is a grouping of related structures called ‘tables,’ which in turn are organized in rows of individual data elements. The rows are often referred to as ‘records,’ and the individual data elements are referred to as ‘fields.’ In this specification generally, therefore, an aggregation of fields is referred to as a ‘data structure’ or a ‘record,’ and an aggregation of records is referred to as a ‘table.’ An aggregation of related tables is called a ‘database.’
A computer program that supports access to information in a database is typically called a database management system or a ‘DBMS.’ A DBMS is responsible for helping other computer programs access, manipulate, and save information in a database. A DBMS typically supports access and management tools to aid users, developers, and other programs in accessing information in a database. One such tool is the structured query language, ‘SQL.’ SQL is query language for requesting information from a database. Although there is a standard of the American National Standards Institute (‘ANSI’) for SQL, as a practical matter, most versions of SQL tend to include many extensions. Here is an example of a database query expressed in SQL:                select * from stores, transactions        where stores.location=“Minnesota”        and stores.storeID=transactions.storeID        
This SQL query accesses information in a database by selecting records from two tables of the database, one table named ‘stores’ and another table named ‘transactions.’ The records selected are those having value “Minnesota” in their store location fields and transactions for the stores in Minnesota. In retrieving the data for this SQL query, a SQL engine will retrieve records from the stores table and records from the transaction table. Records that satisfy the query requirements then are combined in a ‘join.’
To improve the performance of satisfying a query, many databases store the results of previous queries or portions of the results of previous queries and use the stored results to satisfy subsequent queries. Such stored results for performance enhancement are called materialized query tables (‘MQT’). An MQT is a table whose definition is based on the result of a query, and whose data is in the form of pre-computed results that are taken from one or more tables.
An MQT does not have to contain the results of exactly the same query to be useful in subsequent queries. A subsequent database query can sometimes be satisfied using an MQT that is similar to the query. Consider the following MQT and database query.
The MQT:                SELECT e.name empname, d.name deptname        FROM employees e, departments d        WHERE e.dept=d.idmay be used to satisfy the query:        SELECT e.name, d.name        FROM employees e, departments d        WHERE e.dept=d.id        AND d.name=‘Engineering’by selecting records from the MQT such as:        SELECT empname, deptname        FROM mqt        WHERE deptname=‘Engineering’        
In this example the query selects a subset of the results of the MQT. One situation where MQTs are useful therefore includes queries whose results represent a subset of the selected records of the MQT. In some cases, the extra records are a result of having tables joined in the MQT that are not invoked in the query. One existing method of MQT matching useful in a situation where the MQT has one or more tables not invoked in the query is carried out by eliminating the extra table or tables from the MQT using referential integrity reduction until the MQT has no extra tables that are not in the query.
Another way where MQTs could be useful in satisfying the queries may involve expanding the query under certain circumstances to include tables that are joined in the MQT but are not in the query. Consider the following MQT:                SELECT e.name empname, d.name deptname        FROM employees e, departments d        WHERE e.dept=d.id        
And the following query:                SELECT e.name empname        FROM employees e        WHERE e.name LIKE ‘A%’        
In this example, the MQT references the departments table, but this query does not. The MQT may in fact satisfy the query if joining to the departments table is a lossless join. The phrase ‘lossless join’ means a join between a first table and a second table that returns the same number of records as the first table. That is, a lossless join does not produce ‘fan out,’ more records than the first joined table, or ‘fan in,’ less records than the first joined table. The use of the term ‘first table’ and ‘second table’ is simply used to distinguish two tables. There is no order requirement between the first or second table. The lossless join may be lossless with respect to either table. In this example, if the join is lossless, then the join to the departments table in the MQT merely widens the result set to include some department information. Such an MQT may be useful in materialized query table matching if the query can be properly expanded to match the MQT.